package com.elite.groovy.callback

import com.elite.groovy.kh.KHUtil
import com.elite.groovy.util.EliteBean
import com.wisdge.web.springframework.SpringContextUtil
import net.sf.json.JSONObject
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
import javax.sql.DataSource

public class autoAvayaDataDeal implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(autoAvayaDataDeal.class);
    private static JdbcTemplate jt = new JdbcTemplate();
    private static JdbcTemplate o_jt = new JdbcTemplate();
    private static JdbcTemplate a_jt = new JdbcTemplate();

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");
        JSONObject outData = JSONObject.fromObject("{\"code\":1,\"message\":\"操作成功\"}");

        String getMethod = request.getMethod();
        log.debug("HTTP请求调用类型 getMethod:" + getMethod);
        if (getMethod != "POST" && getMethod != "GET") {
            outData.put("code", -1);
            outData.put("message", "HTTP请求调用类型错误，接口调用失败：" + getMethod);
            log.error(outData);
            return outData;
        }

        // 获取所有Header
        Map<String, String> mapHeader = new HashMap<String, String>();
        Enumeration headerNames = request.getHeaderNames();
        while (headerNames.hasMoreElements()) {
            String key = (String) headerNames.nextElement();
            String value = request.getHeader(key);
            log.debug("Call Header-" + key + ":" + value);

            mapHeader.put(key, value);
        }

        // 获取所有参数名
        Map<String, String> mapParam = new HashMap<String, String>();
        Enumeration ep = request.getParameterNames();
        while (ep.hasMoreElements()) {
            String key = (String) ep.nextElement();
            String value = request.getParameter(key);
            log.debug("Call Parameter-" + key + ":" + value);
            mapParam.put(key, value);

        }

        // 初始化数据源
        EliteBean eliteBean2 = SpringContextUtil.getBean("ebean");
        log.debug("eliteBean2:" + eliteBean2);

        DataSource DataSource2 = eliteBean2.getDataSource();
        log.debug("DataSource2:" + DataSource2);

        jt = new JdbcTemplate(DataSource2);
        log.debug("JdbcTemplate:" + jt);

        // 获取Oracle数据库的连接
        a_jt = eliteBean.getJdbcTemplate("dataSourceOracle");
        // 初始化Oracle连接session以支持字符串转日期隐式转换
        a_jt.update("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'");

        // 获取调用数据
        String Syncpram = "";
        if (getMethod.equals("GET")) {
            Syncpram = request.getQueryString();
            log.info("接收调用GET参数信息：" + Syncpram);
        } else if (getMethod.equals("POST")) {
            InputStreamReader reader = new InputStreamReader(request.getInputStream(), "UTF-8");
            BufferedReader iny = new BufferedReader(reader);
            String line;
            while ((line = iny.readLine()) != null) {
                Syncpram += line;
            }
            log.info("接收调用POST报文信息：" + System.getProperty("line.separator") + Syncpram);
        }

        try {
            String op_type = request.getParameter("op_type");
            String op_result = "";

            if (op_type.equals("1")) {
                // 满意度数据 Oracle -> OceanBase
                WMSK_STATISFACTION(2000);
            } else if (op_type.equals("2")) {
                // IVR轨迹明细 OceanBase -> Oracle
                IVR_LOG_FOUNDATION(2000);
            } else if (op_type.equals("3")) {

                // 排班数据 OceanBase -> Oracle
                op_result = VIEW_CLASSES_TABLE(2000);
                outData.put("message", "排班数据结果：" + op_result);
            } else {
                outData.put("code", 0);
                outData.put("message", "操作类型错误无法识别：" + op_type);
            }
        } catch (Exception e) {
            outData.put("code", -1);
            outData.put("message", "HTTP请求调用错误，接口处理异常：" + e.getMessage());
            log.error(outData, e);

        }

        return outData;
    }


    // 1-WMSK_STATISFACTION满意度数据
    public static void WMSK_STATISFACTION(int Limit_Num) {

        String get_Data_SQL = "SELECT STATISFACTION_GUID, CUSTOMER_GUID, STAFF_ID, PHONENO, STATISFACTION, to_char(CREATEDDATE,'YYYY-mm-dd hh24:mi:ss') As CREATEDDATE, ROLEGROUPID, EVENT_GUID, SKILL_DESC, UCID, YES_OR_ISSUE, STATISFACTION1, DNIS, BAD_DESC, TASKSTATUS, OBJECTIVE_GUID FROM WMSK_STATISFACTION WHERE CREATEDDATE > TO_DATE(TO_CHAR(SYSDATE -1/24, 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') AND ROWNUM <= " + Limit_Num + " ORDER BY CREATEDDATE DESC";

        // 执行调用
        List<Map<String, Object>> res_Data_List = a_jt.queryForList(get_Data_SQL);
        log.debug("1-WMSK_STATISFACTION满意度数据 toDoGet Data:" + res_Data_List.size());

        int insert_Num = 0;
        int update_Num = 0;
        for (int i = 0; i < res_Data_List.size(); i++) {
            log.debug("1-WMSK_STATISFACTION满意度数据 res_Data_List Data:" + res_Data_List.get(i).toString());

            String STATISFACTION_GUID = KHUtil.GetMapV(res_Data_List.get(i), "STATISFACTION_GUID");
            String CUSTOMER_GUID = KHUtil.GetMapV(res_Data_List.get(i), "CUSTOMER_GUID");
            String STAFF_ID = KHUtil.GetMapV(res_Data_List.get(i), "STAFF_ID");
            String PHONENO = KHUtil.GetMapV(res_Data_List.get(i), "PHONENO");
            String STATISFACTION = KHUtil.GetMapV(res_Data_List.get(i), "STATISFACTION");
            String CREATEDDATE = KHUtil.GetMapV(res_Data_List.get(i), "CREATEDDATE");
            String ROLEGROUPID = KHUtil.GetMapV(res_Data_List.get(i), "ROLEGROUPID");
            String EVENT_GUID = KHUtil.GetMapV(res_Data_List.get(i), "EVENT_GUID");
            String SKILL_DESC = KHUtil.GetMapV(res_Data_List.get(i), "SKILL_DESC");
            String UCID = KHUtil.GetMapV(res_Data_List.get(i), "UCID");
            String YES_OR_ISSUE = KHUtil.GetMapV(res_Data_List.get(i), "YES_OR_ISSUE");
            String STATISFACTION1 = KHUtil.GetMapV(res_Data_List.get(i), "STATISFACTION1");
            String DNIS = KHUtil.GetMapV(res_Data_List.get(i), "DNIS");
            String BAD_DESC = KHUtil.GetMapV(res_Data_List.get(i), "BAD_DESC");
            String TASKSTATUS = KHUtil.GetMapV(res_Data_List.get(i), "TASKSTATUS");
            String OBJECTIVE_GUID = KHUtil.GetMapV(res_Data_List.get(i), "OBJECTIVE_GUID");
            log.debug("1-WMSK_STATISFACTION满意度数据: STATISFACTION_GUID:" + STATISFACTION_GUID + " CREATEDDATE:" + CREATEDDATE);

            // 检查当前数据库是否存在 存在更新 不存在新建
            String sql = "SELECT COUNT(*) AS COUNT FROM WMSK_STATISFACTION WHERE STATISFACTION_GUID = ?";
            List<Map<String, Object>> sqlResult = jt.queryForList(sql, STATISFACTION_GUID);
            int Countnum = Integer.parseInt(sqlResult.get(0).get("COUNT").toString());

            if (Countnum <= 0) {

                sql = "INSERT INTO WMSK_STATISFACTION (STATISFACTION_GUID, CUSTOMER_GUID, STAFF_ID, PHONENO, STATISFACTION, CREATEDDATE, ROLEGROUPID, EVENT_GUID, SKILL_DESC, UCID, YES_OR_ISSUE, STATISFACTION1, DNIS, BAD_DESC, TASKSTATUS, OBJECTIVE_GUID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                jt.update(sql, STATISFACTION_GUID, CUSTOMER_GUID, STAFF_ID, PHONENO, STATISFACTION, CREATEDDATE, ROLEGROUPID, EVENT_GUID, SKILL_DESC, UCID, YES_OR_ISSUE, STATISFACTION1, DNIS, BAD_DESC, TASKSTATUS, OBJECTIVE_GUID);
                insert_Num++;

            } else {
                sql = "UPDATE WMSK_STATISFACTION SET CUSTOMER_GUID = ?, STAFF_ID = ?, PHONENO = ?, STATISFACTION = ?, CREATEDDATE = ?, ROLEGROUPID = ?, EVENT_GUID = ?, SKILL_DESC = ?, UCID = ?, YES_OR_ISSUE = ?, STATISFACTION1 = ?, DNIS = ?, BAD_DESC = ?, TASKSTATUS = ?, OBJECTIVE_GUID = ? WHERE STATISFACTION_GUID = ?";

                jt.update(sql, CUSTOMER_GUID, STAFF_ID, PHONENO, STATISFACTION, CREATEDDATE, ROLEGROUPID, EVENT_GUID, SKILL_DESC, UCID, YES_OR_ISSUE, STATISFACTION1, DNIS, BAD_DESC, TASKSTATUS, OBJECTIVE_GUID, STATISFACTION_GUID);
                update_Num++;
            }
        }
        log.debug("1-WMSK_STATISFACTION满意度数据 回写完成 总数:" + res_Data_List.size() + " 新建:" + insert_Num + " 更新:" + update_Num);

    }

    // 2-IVR轨迹明细
    public static void IVR_LOG_FOUNDATION(int Limit_Num) {

        String get_Data_SQL = "SELECT LOG_ID, CALL_ID, CLNT_ID, CLNT_TYPE, CLNT_BELG_ORG, CLNT_BELG_CHNEL, CLNT_NAME, CLNT_SEX, CLNT_AREA, CLNT_LEVEL, CLNT_FEELING, CLNT_IDNUM, CLNT_INFO_FLAG, ANI, DNIS, CALL_IN_TYPE, AREA_GROUP_NO, AREA_NO, AREA_CITY_NAME, AREA_PROV_NAME, AREA_PROV_NO, START_DATE, START_TIME, END_DATE, END_TIME, TO_AGENT_IF, TO_AGENT_TYPE, TO_AGENT_IF_SUCCESS, LEAVEWORD_IF, FAIL_IF_DEAL, AGENT_ID, TRACE, CHANNEL, IVR_NODE, DEAL_DESC, DEAL_TIME, DEAL_WITH_PERSON, START_TIME_STR, END_TIME_STR FROM IVR_LOG_FOUNDATION WHERE START_TIME_STR > TO_CHAR(SYSDATE -1/24, 'yyyy-mm-dd HH24:MI:SS') AND ROWNUM <= " + Limit_Num + " ORDER BY START_TIME_STR DESC";

        // 执行调用
        List<Map<String, Object>> res_Data_List = a_jt.queryForList(get_Data_SQL);
        log.debug("2-IVR轨迹明细 toDoGet Data:" + res_Data_List.size());

        int insert_Num = 0;
        int update_Num = 0;
        for (int i = 0; i < res_Data_List.size(); i++) {
            log.debug("2-IVR轨迹明细 res_Data_List Data:" + res_Data_List.get(i).toString());

            String LOG_ID = KHUtil.GetMapV(res_Data_List.get(i), "LOG_ID");
            String CALL_ID = KHUtil.GetMapV(res_Data_List.get(i), "CALL_ID");
            String CLNT_ID = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_ID");
            String CLNT_TYPE = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_TYPE");
            String CLNT_BELG_ORG = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_BELG_ORG");
            String CLNT_BELG_CHNEL = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_BELG_CHNEL");
            String CLNT_NAME = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_NAME");
            String CLNT_SEX = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_SEX");
            String CLNT_AREA = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_AREA");
            String CLNT_LEVEL = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_LEVEL");
            String CLNT_FEELING = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_FEELING");
            String CLNT_IDNUM = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_IDNUM");
            String CLNT_INFO_FLAG = KHUtil.GetMapV(res_Data_List.get(i), "CLNT_INFO_FLAG");
            String ANI = KHUtil.GetMapV(res_Data_List.get(i), "ANI");
            String DNIS = KHUtil.GetMapV(res_Data_List.get(i), "DNIS");
            String CALL_IN_TYPE = KHUtil.GetMapV(res_Data_List.get(i), "CALL_IN_TYPE");
            String AREA_GROUP_NO = KHUtil.GetMapV(res_Data_List.get(i), "AREA_GROUP_NO");
            String AREA_NO = KHUtil.GetMapV(res_Data_List.get(i), "AREA_NO");
            String AREA_CITY_NAME = KHUtil.GetMapV(res_Data_List.get(i), "AREA_CITY_NAME");
            String AREA_PROV_NAME = KHUtil.GetMapV(res_Data_List.get(i), "AREA_PROV_NAME");
            String AREA_PROV_NO = KHUtil.GetMapV(res_Data_List.get(i), "AREA_PROV_NO");
            String START_DATE = KHUtil.GetMapV(res_Data_List.get(i), "START_DATE");
            String START_TIME = KHUtil.GetMapV(res_Data_List.get(i), "START_TIME");
            String END_DATE = KHUtil.GetMapV(res_Data_List.get(i), "END_DATE");
            String END_TIME = KHUtil.GetMapV(res_Data_List.get(i), "END_TIME");
            String TO_AGENT_IF = KHUtil.GetMapV(res_Data_List.get(i), "TO_AGENT_IF");
            String TO_AGENT_TYPE = KHUtil.GetMapV(res_Data_List.get(i), "TO_AGENT_TYPE");
            String TO_AGENT_IF_SUCCESS = KHUtil.GetMapV(res_Data_List.get(i), "TO_AGENT_IF_SUCCESS");
            String LEAVEWORD_IF = KHUtil.GetMapV(res_Data_List.get(i), "LEAVEWORD_IF");
            String FAIL_IF_DEAL = KHUtil.GetMapV(res_Data_List.get(i), "FAIL_IF_DEAL");
            String AGENT_ID = KHUtil.GetMapV(res_Data_List.get(i), "AGENT_ID");
            String TRACE = KHUtil.GetMapV(res_Data_List.get(i), "TRACE");
            String CHANNEL = KHUtil.GetMapV(res_Data_List.get(i), "CHANNEL");
            String IVR_NODE = KHUtil.GetMapV(res_Data_List.get(i), "IVR_NODE");
            String DEAL_DESC = KHUtil.GetMapV(res_Data_List.get(i), "DEAL_DESC");
            String DEAL_TIME = KHUtil.GetMapV(res_Data_List.get(i), "DEAL_TIME");
            String DEAL_WITH_PERSON = KHUtil.GetMapV(res_Data_List.get(i), "DEAL_WITH_PERSON");
            String START_TIME_STR = KHUtil.GetMapV(res_Data_List.get(i), "START_TIME_STR");
            String END_TIME_STR = KHUtil.GetMapV(res_Data_List.get(i), "END_TIME_STR");
            log.debug("2-IVR轨迹明细: LOG_ID:" + LOG_ID + " START_TIME_STR:" + START_TIME_STR);

            // 检查当前数据库是否存在 存在更新 不存在新建
            String sql = "SELECT COUNT(*) AS COUNT FROM IVR_LOG_FOUNDATION WHERE LOG_ID = ?";
            List<Map<String, Object>> sqlResult = jt.queryForList(sql, LOG_ID);
            int Countnum = Integer.parseInt(sqlResult.get(0).get("COUNT").toString());

            if (Countnum <= 0) {

                sql = "INSERT INTO IVR_LOG_FOUNDATION (LOG_ID, CALL_ID, CLNT_ID, CLNT_TYPE, CLNT_BELG_ORG, CLNT_BELG_CHNEL, CLNT_NAME, CLNT_SEX, CLNT_AREA, CLNT_LEVEL, CLNT_FEELING, CLNT_IDNUM, CLNT_INFO_FLAG, ANI, DNIS, CALL_IN_TYPE, AREA_GROUP_NO, AREA_NO, AREA_CITY_NAME, AREA_PROV_NAME, AREA_PROV_NO, START_DATE, START_TIME, END_DATE, END_TIME, TO_AGENT_IF, TO_AGENT_TYPE, TO_AGENT_IF_SUCCESS, LEAVEWORD_IF, FAIL_IF_DEAL, AGENT_ID, TRACE, CHANNEL, IVR_NODE, DEAL_DESC, DEAL_TIME, DEAL_WITH_PERSON, START_TIME_STR, END_TIME_STR) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                jt.update(sql, LOG_ID, CALL_ID, CLNT_ID, CLNT_TYPE, CLNT_BELG_ORG, CLNT_BELG_CHNEL, CLNT_NAME, CLNT_SEX, CLNT_AREA, CLNT_LEVEL, CLNT_FEELING, CLNT_IDNUM, CLNT_INFO_FLAG, ANI, DNIS, CALL_IN_TYPE, AREA_GROUP_NO, AREA_NO, AREA_CITY_NAME, AREA_PROV_NAME, AREA_PROV_NO, START_DATE, START_TIME, END_DATE, END_TIME, TO_AGENT_IF, TO_AGENT_TYPE, TO_AGENT_IF_SUCCESS, LEAVEWORD_IF, FAIL_IF_DEAL, AGENT_ID, TRACE, CHANNEL, IVR_NODE, DEAL_DESC, DEAL_TIME, DEAL_WITH_PERSON, START_TIME_STR, END_TIME_STR);
                insert_Num++;

            } else {
                sql = "UPDATE IVR_LOG_FOUNDATION SET CALL_ID = ?, CLNT_ID = ?, CLNT_TYPE = ?, CLNT_BELG_ORG = ?, CLNT_BELG_CHNEL = ?, CLNT_NAME = ?, CLNT_SEX = ?, CLNT_AREA = ?, CLNT_LEVEL = ?, CLNT_FEELING = ?, CLNT_IDNUM = ?, CLNT_INFO_FLAG = ?, ANI = ?, DNIS = ?, CALL_IN_TYPE = ?, AREA_GROUP_NO = ?, AREA_NO = ?, AREA_CITY_NAME = ?, AREA_PROV_NAME = ?, AREA_PROV_NO = ?, START_DATE = ?, START_TIME = ?, END_DATE = ?, END_TIME = ?, TO_AGENT_IF = ?, TO_AGENT_TYPE = ?, TO_AGENT_IF_SUCCESS = ?, LEAVEWORD_IF = ?, FAIL_IF_DEAL = ?, AGENT_ID = ?, TRACE = ?, CHANNEL = ?, IVR_NODE = ?, DEAL_DESC = ?, DEAL_TIME = ?, DEAL_WITH_PERSON = ?, START_TIME_STR = ?, END_TIME_STR = ? WHERE LOG_ID = ?";

                jt.update(sql, CALL_ID, CLNT_ID, CLNT_TYPE, CLNT_BELG_ORG, CLNT_BELG_CHNEL, CLNT_NAME, CLNT_SEX, CLNT_AREA, CLNT_LEVEL, CLNT_FEELING, CLNT_IDNUM, CLNT_INFO_FLAG, ANI, DNIS, CALL_IN_TYPE, AREA_GROUP_NO, AREA_NO, AREA_CITY_NAME, AREA_PROV_NAME, AREA_PROV_NO, START_DATE, START_TIME, END_DATE, END_TIME, TO_AGENT_IF, TO_AGENT_TYPE, TO_AGENT_IF_SUCCESS, LEAVEWORD_IF, FAIL_IF_DEAL, AGENT_ID, TRACE, CHANNEL, IVR_NODE, DEAL_DESC, DEAL_TIME, DEAL_WITH_PERSON, START_TIME_STR, END_TIME_STR, LOG_ID);
                update_Num++;
            }
        }
        log.debug("2-IVR轨迹明细 回写完成 总数:" + res_Data_List.size() + " 新建:" + insert_Num + " 更新:" + update_Num);

    }

    // 3-排班数据
    public static String VIEW_CLASSES_TABLE(int Limit_Num) {
        String op_result = "";
        op_result = VIEW_CLASSES_TABLE_Staff(Limit_Num);
        op_result = op_result + VIEW_CLASSES_TABLE_t_Pb_Bclist(Limit_Num);
        op_result = op_result + VIEW_CLASSES_TABLE_t_Pb_Staffresult(Limit_Num);
        return op_result;

    }

    // 3-1-排班数据-Staff
    public static String VIEW_CLASSES_TABLE_Staff(int Limit_Num) {
        String op_result = "";

        String get_Data_SQL = "Select Staff_Id, Agent_Id, Extension, Staffname, Mobile, Defaultrolegroup, Workstatus, Createdby, To_Char(Createddate, 'yyyy-mm-dd hh24:mi:ss') AS Createddate, Modifiedby,To_Char(Modifieddate, 'yyyy-mm-dd hh24:mi:ss') AS Modifieddate, Loginname from Staff";

        // 执行调用
        List<Map<String, Object>> res_Data_List = jt.queryForList(get_Data_SQL);
        log.debug("3-排班数据-Staff toDoGet Data:" + res_Data_List.size());

        int insert_Num = 0;
        int update_Num = 0;
        for (int i = 0; i < res_Data_List.size(); i++) {
            log.debug("3-排班数据-Staff res_Data_List Data:" + res_Data_List.get(i).toString());

            String Staff_Id = KHUtil.GetMapV(res_Data_List.get(i), "Staff_Id");
            String Agent_Id = KHUtil.GetMapV(res_Data_List.get(i), "Agent_Id");
            String Extension = KHUtil.GetMapV(res_Data_List.get(i), "Extension");
            String Staffname = KHUtil.GetMapV(res_Data_List.get(i), "Staffname");
            String Mobile = KHUtil.GetMapV(res_Data_List.get(i), "Mobile");
            String Defaultrolegroup = KHUtil.GetMapV(res_Data_List.get(i), "Defaultrolegroup");
            String Workstatus = KHUtil.GetMapV(res_Data_List.get(i), "Workstatus");
            String Createdby = KHUtil.GetMapV(res_Data_List.get(i), "Createdby");
            String Createddate = KHUtil.GetMapV(res_Data_List.get(i), "Createddate");
            String Modifiedby = KHUtil.GetMapV(res_Data_List.get(i), "Modifiedby");
            String Modifieddate = KHUtil.GetMapV(res_Data_List.get(i), "Modifieddate");
            String Loginname = KHUtil.GetMapV(res_Data_List.get(i), "Loginname");

            // 检查当前数据库是否存在 存在更新 不存在新建
            String sql = "SELECT COUNT(*) AS COUNT FROM Staff WHERE Staff_Id = ?";
            List<Map<String, Object>> sqlResult = a_jt.queryForList(sql, Staff_Id);
            int Countnum = Integer.parseInt(sqlResult.get(0).get("COUNT").toString());

            if (Countnum <= 0) {

                sql = "Insert Into Staff (Staff_Id, Agent_Id, Extension, Staffname, Mobile, Defaultrolegroup, Workstatus, Createdby, Createddate, Modifiedby, Modifieddate, Loginname) Values ('" + Staff_Id + "', '" + Agent_Id + "', '" + Extension + "', '" + Staffname + "', '" + Mobile + "', '" + Defaultrolegroup + "', '" + Workstatus + "', '" + Createdby + "', To_Date('" + Createddate + "', 'yyyy-mm-dd hh24:mi:ss'), '" + Modifiedby + "', To_Date('" + Modifieddate + "', 'yyyy-mm-dd hh24:mi:ss'), '" + Loginname + "')";
                log.debug("3-排班数据-Staff DB SQL:" + sql);

                a_jt.update(sql);
                insert_Num++;

            } else {
                sql = "Update Staff Set Agent_Id = '" + Agent_Id + "', Extension = '" + Extension + "', Staffname = '" + Staffname + "', Mobile = '" + Mobile + "', Defaultrolegroup = '" + Defaultrolegroup + "', Workstatus = '" + Workstatus + "', Createdby = '" + Createdby + "', Createddate = To_Date('" + Createddate + "', 'yyyy-mm-dd hh24:mi:ss'), Modifiedby = '" + Modifiedby + "', Modifieddate = To_Date('" + Modifieddate + "', 'yyyy-mm-dd hh24:mi:ss'), Loginname = '" + Loginname + "' Where Staff_Id = '" + Staff_Id + "'";
                log.debug("3-排班数据-Staff DB SQL:" + sql);

                a_jt.update(sql);
                update_Num++;
            }
        }

        op_result = "3-排班数据-Staff 回写完成 总数:" + res_Data_List.size() + " 新建:" + insert_Num + " 更新:" + update_Num;
        log.debug(op_result);

        return op_result;

    }

    // 3-2-排班数据-t_Pb_Bclist
    public static String VIEW_CLASSES_TABLE_t_Pb_Bclist(int Limit_Num) {
        String op_result = "";

        String get_Data_SQL = "Select Bcgrpid, Bclistid, Bcdesc, Starttime1, Endtime1, Starttime2, Endtime2, Starttime3, Endtime3, To_Char(Createdate, 'yyyy-mm-dd hh24:mi:ss') As Createdate, Createby, To_Char(Modifydate, 'yyyy-mm-dd hh24:mi:ss') As Modifydate, Modifyby, Yesclass, To_Char(Yestime, 'yyyy-mm-dd hh24:mi:ss') As Yestime From t_Pb_Bclist Order By Modifydate Desc";

        // 执行调用
        List<Map<String, Object>> res_Data_List = jt.queryForList(get_Data_SQL);
        log.debug("3-排班数据-t_Pb_Bclist toDoGet Data:" + res_Data_List.size());

        int insert_Num = 0;
        int update_Num = 0;
        for (int i = 0; i < res_Data_List.size(); i++) {
            log.debug("3-排班数据-t_Pb_Bclist res_Data_List Data:" + res_Data_List.get(i).toString());

            String Bclistid = KHUtil.GetMapV(res_Data_List.get(i), "Bclistid");
            String Bcgrpid = KHUtil.GetMapV(res_Data_List.get(i), "Bcgrpid");
            String Bcdesc = KHUtil.GetMapV(res_Data_List.get(i), "Bcdesc");
            String Starttime1 = KHUtil.GetMapV(res_Data_List.get(i), "Starttime1");
            String Endtime1 = KHUtil.GetMapV(res_Data_List.get(i), "Endtime1");
            String Starttime2 = KHUtil.GetMapV(res_Data_List.get(i), "Starttime2");
            String Endtime2 = KHUtil.GetMapV(res_Data_List.get(i), "Endtime2");
            String Starttime3 = KHUtil.GetMapV(res_Data_List.get(i), "Starttime3");
            String Endtime3 = KHUtil.GetMapV(res_Data_List.get(i), "Endtime3");
            String Createdate = KHUtil.GetMapV(res_Data_List.get(i), "Createdate");
            String Createby = KHUtil.GetMapV(res_Data_List.get(i), "Createby");
            String Modifydate = KHUtil.GetMapV(res_Data_List.get(i), "Modifydate");
            String Modifyby = KHUtil.GetMapV(res_Data_List.get(i), "Modifyby");
            String Yesclass = KHUtil.GetMapV(res_Data_List.get(i), "Yesclass");
            String Yestime = KHUtil.GetMapV(res_Data_List.get(i), "Yestime");

            // 检查当前数据库是否存在 存在更新 不存在新建
            String sql = "SELECT COUNT(*) AS COUNT FROM t_Pb_Bclist WHERE Bclistid = ?";
            List<Map<String, Object>> sqlResult = a_jt.queryForList(sql, Bclistid);
            int Countnum = Integer.parseInt(sqlResult.get(0).get("COUNT").toString());

            if (Countnum <= 0) {

                sql = "Insert Into t_Pb_Bclist (Bcgrpid, Bclistid, Bcdesc, Starttime1, Endtime1, Starttime2, Endtime2, Starttime3, Endtime3, Createdate, Createby, Modifydate, Modifyby, Yesclass, Yestime) Values ('" + Bcgrpid + "', '" + Bclistid + "', '" + Bcdesc + "', '" + Starttime1 + "', '" + Endtime1 + "', '" + Starttime2 + "', '" + Endtime2 + "', '" + Starttime2 + "', '" + Endtime2 + "', To_Date('" + Createdate + "', 'yyyy-mm-dd hh24:mi:ss'), '" + Createby + "', To_Date('" + Modifydate + "', 'yyyy-mm-dd hh24:mi:ss'), '" + Modifyby + "', '" + Yesclass + "', To_Date('" + Yestime + "', 'yyyy-mm-dd hh24:mi:ss'))";
                log.debug("3-排班数据-t_Pb_Bclist DB SQL:" + sql);

                a_jt.update(sql);
                insert_Num++;

            } else {
                sql = "Update t_Pb_Bclist Set Bcgrpid = '" + Bcgrpid + "', Bcdesc = '" + Bcdesc + "', Starttime1 = '" + Starttime1 + "', Endtime1 = '" + Endtime1 + "', Starttime2 = '" + Starttime2 + "', Endtime2 = '" + Endtime2 + "', Starttime3 = '" + Starttime3 + "', Endtime3 = '" + Endtime3 + "', Createdate = To_Date('" + Createdate + "', 'yyyy-mm-dd hh24:mi:ss'), Createby = '" + Createby + "', Modifydate = To_Date('" + Modifydate + "', 'yyyy-mm-dd hh24:mi:ss'), Modifyby = '" + Modifyby + "', Yesclass = '" + Yesclass + "', Yestime = To_Date('" + Yestime + "', 'yyyy-mm-dd hh24:mi:ss') Where Bclistid = '" + Bclistid + "'";
                log.debug("3-排班数据-t_Pb_Bclist DB SQL:" + sql);

                a_jt.update(sql);
                update_Num++;
            }
        }

        op_result = "3-排班数据-t_Pb_Bclist 回写完成 总数:" + res_Data_List.size() + " 新建:" + insert_Num + " 更新:" + update_Num;
        log.debug(op_result);

        return op_result;

    }

    // 3-3-排班数据-t_Pb_Staffresult
    public static String VIEW_CLASSES_TABLE_t_Pb_Staffresult(int Limit_Num) {
        String op_result = "";

        String tmp_Templist_Guid = KHUtil.getRandomString(36);

        String get_Data_SQL = "Select Mainid, Keyid, Staff_Id, To_Char(Workday, 'yyyy-mm-dd hh24:mi:ss') As Workday, Bclistid, Freeflag, Specialid, '' as Templist_Guid, Overtimeid, Overtimeflag From t_Pb_Staffresult  Where Workday >= Sysdate - 3 Order By Workday Desc";

        // 执行调用
        List<Map<String, Object>> res_Data_List = jt.queryForList(get_Data_SQL);
        log.debug("3-排班数据-t_Pb_Staffresult toDoGet Data:" + res_Data_List.size());

        int insert_Num = 0;
        int update_Num = 0;
        int fail_Num = 0;
        for (int i = 0; i < res_Data_List.size(); i++) {
            log.debug("3-排班数据-t_Pb_Staffresult res_Data_List Data:" + res_Data_List.get(i).toString());
            op_result = res_Data_List.get(i).toString();
            String Keyid = "";
            try {

                Keyid = KHUtil.GetMapV(res_Data_List.get(i), "Keyid");
                String Mainid = KHUtil.GetMapV(res_Data_List.get(i), "Mainid");
                String Staff_Id = KHUtil.GetMapV(res_Data_List.get(i), "Staff_Id");
                String Workday = KHUtil.GetMapV(res_Data_List.get(i), "Workday");
                String Bclistid = KHUtil.GetMapV(res_Data_List.get(i), "Bclistid");
                String Freeflag = KHUtil.GetMapV(res_Data_List.get(i), "Freeflag");
                String Specialid = KHUtil.GetMapV(res_Data_List.get(i), "Specialid");
                String Overtimeid = KHUtil.GetMapV(res_Data_List.get(i), "Overtimeid");
                String Overtimeflag = KHUtil.GetMapV(res_Data_List.get(i), "Overtimeflag");

                // 检查当前数据库是否存在 存在更新 不存在新建
                String sql = "SELECT COUNT(*) AS COUNT FROM t_Pb_Staffresult WHERE Keyid = ?";
                List<Map<String, Object>> sqlResult = a_jt.queryForList(sql, Keyid);
                int Countnum = Integer.parseInt(sqlResult.get(0).get("COUNT").toString());

                if (Countnum <= 0) {

                    sql = "Insert Into t_Pb_Staffresult (Mainid, Keyid, Staff_Id, Workday, Bclistid, Freeflag, Specialid, Overtimeid, Overtimeflag, Templist_Guid) Values ('" + Mainid + "', '" + Keyid + "', '" + Staff_Id + "', To_Date('" + Workday + "', 'yyyy-mm-dd hh24:mi:ss'), '" + Bclistid + "', '" + Freeflag + "', '" + Specialid + "', '" + Overtimeid + "', '" + Overtimeflag + "', '" + tmp_Templist_Guid + "')";
                    log.debug("3-排班数据-t_Pb_Staffresult DB SQL:" + sql);

                    a_jt.update(sql);
                    insert_Num++;

                } else {
                    sql = "Update t_Pb_Staffresult Set Mainid = '" + Mainid + "', Staff_Id = '" + Staff_Id + "', Workday = To_Date('" + Workday + "', 'yyyy-mm-dd hh24:mi:ss'), Bclistid = '" + Bclistid + "', Freeflag = '" + Freeflag + "', Specialid = '" + Specialid + "', Overtimeid = '" + Overtimeid + "', Overtimeflag = '" + Overtimeflag + "', Templist_Guid = '" + tmp_Templist_Guid + "' Where Keyid = '" + Keyid + "'";
                    log.debug("3-排班数据-t_Pb_Staffresult DB SQL:" + sql);

                    a_jt.update(sql);
                    update_Num++;
                }

            } catch (Exception e) {
                fail_Num++;
                op_result = "t_Pb_Staffresult:" + Keyid + " 处理异常:" + e.getMessage();
                log.warn(op_result, e);
            }
        }

        // 将本次没有更新到的数据删除
        int del_Num = a_jt.update("Delete From t_Pb_Staffresult  Where Workday >= Sysdate - 3 And Templist_Guid != ?", tmp_Templist_Guid);
        log.debug("3-排班数据-将本次没有更新到的数据删除t_Pb_Staffresult DB Num:" + del_Num);

        op_result = op_result + "3-排班数据-t_Pb_Staffresult 回写完成 总数:" + res_Data_List.size() + " 新建:" + insert_Num + " 更新:" + update_Num + " 失败:" + fail_Num + " 删除:" + del_Num;
        log.debug(op_result);

        return op_result;

    }
}